package cn.finedo.highway.quartz;

import cn.finedo.common.date.DateUtil;
import cn.finedo.common.domain.CommonDataDomain;
import cn.finedo.common.domain.ReturnValueDomain;
import cn.finedo.common.non.NonUtil;
import cn.finedo.fsdp.service.common.jdbc.JdbcTemplate;
import cn.finedo.highway.domain.VariableDomain;
import cn.finedo.highway.domain.Constant;
import cn.finedo.highway.domain.SqlDomain;
import cn.finedo.highway.service.DataLogService;
import cn.finedo.highway.util.thread.FutureUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.TimeUnit;
import java.util.stream.Collectors;

/**
 * @description:定时统计高速联网大屏数据
 * @author: lizheng
 * @createDate: 2023-2-18
 * @version: 1.0
 */
@Component
public class DataStaQuartz {

    private final Logger logger = LoggerFactory.getLogger(this.getClass());

    @Resource(name = "fsdpJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    @Resource(name = "wayJdbcTemplate")
    private JdbcTemplate wayJdbcTemplate;

    @Autowired
    private DataLogService logService;

    /**
     * 每日0点10分定时执行数据汇总表数据统计任务
     * @throws Exception
     */
    @Scheduled(cron = "10 00 00 * * ?")
//    @PostConstruct
    public void dataStatisticTask() {
        logger.info("定时执行数据汇总表数据统计，开始....");
        ReturnValueDomain<Object> ret =dataStatistic();
        logger.info(ret.getResultdesc());
    }

    public ReturnValueDomain<Object> dataStatistic(){
        ReturnValueDomain<Object> ret = new ReturnValueDomain<>();
        // 查询数据库highway，得到数据统计基础信息
        String allsql = "select a.dsid,a.dsname,a.tablename,b.columnname,b.valuetype,b.valuescript,b.isprkey \n" +
                "from LW_DATASTATSQL a \n" +
                "left join LW_DATASTATSQLATTR b on a.dsid = b.dsid where a.dsid in  ('1') ";
        List<CommonDataDomain> allData = new ArrayList<>();
        try{
            allData =  jdbcTemplate.query(allsql);
        }catch(Exception e){
            logger.error("查询数据汇总表失败"+e);
            return ret.setFail("查询数据汇总表失败"+e);
        }
        // 根据数据统计基础信息筛选出的SQL和基础常量(变量)
        Map<String, List<VariableDomain>> baseMap = new HashMap<>();
        List<SqlDomain> sqlList = new ArrayList<>();
        dataprocess(allData,baseMap,sqlList);

        // 根据SQL的个数开启多线程,每条sql都进行统计的操作
        List<CompletableFuture<ReturnValueDomain<Object>>> completableFutureList =
                sqlList.parallelStream().map(sqlDomain -> FutureUtil.supplyAsync(
                        3000000, TimeUnit.MILLISECONDS, () -> {
                            // 查询源数据库的方法
                            return statisticsMethod(sqlDomain,null,null);
                        }
                ).exceptionally((error) -> {
                    //通过exceptionally 捕获线程异常
                    error.printStackTrace();
                    ReturnValueDomain<Object> errorRet= new ReturnValueDomain<>();
                    return errorRet.setFail(error.getMessage());
                })).collect(Collectors.toList());
        // 等待所有结果结束
//        CompletableFuture.allOf(completableFutureList.toArray(new CompletableFuture[0])).join();
        // 获取所有结果
//        List<ReturnValueDomain<Object>> retList = completableFutureList.stream().map(
//                CompletableFuture::join).collect(Collectors.toList());
        return ret.setSuccess("数据汇总表数据统计开始");
    }

    /**
     * 根据数据统计基础信息筛选出的SQL和基础常量(变量)
     * @param allData 所有数据
     * @param baseMap Type为常量(变量)的数据
     * @param sqlList Type为SQL的数据
     */
    public void dataprocess(List<CommonDataDomain> allData, Map<String, List<VariableDomain>> baseMap, List<SqlDomain> sqlList){
        for(CommonDataDomain dataJson : allData){
            String dsid = dataJson.getString("DSID");
            if(!baseMap.containsKey(dsid)){
                List<VariableDomain> baselist = new ArrayList<>();
                baseMap.put(dsid,baselist);
            }

            // 筛选出SQL类型的数据
            if(dataJson.getString("VALUETYPE").equals("SQL")){
                SqlDomain sqlDomain = new SqlDomain();
                sqlDomain.setBaselist(baseMap.get(dataJson.getString("DSID")));
                sqlDomain.setTableName(dataJson.getString("TABLENAME"));
                sqlDomain.setColumnname(dataJson.getString("COLUMNNAME"));
                sqlDomain.setDsid(dataJson.getString("DSID"));
                sqlDomain.setSql(dataJson.getString("VALUESCRIPT"));
                sqlList.add(sqlDomain);
            }else{
                // 筛选出变量或常量
                List<VariableDomain> noSqlList = baseMap.get(dsid);
                String columnname = dataJson.getString("COLUMNNAME");
                String valuescript = dataJson.getString("VALUESCRIPT");
                String isprkey = dataJson.getString("ISPRKEY");
                String valuetype = dataJson.getString("VALUETYPE");
                VariableDomain baseDomain = new VariableDomain();
                baseDomain.setIsprkey(isprkey);
                baseDomain.setValuescript(valuescript);
                baseDomain.setValuetype(valuetype);
                baseDomain.setColumnname(columnname);
                noSqlList.add(baseDomain);
            }
        }
    }

    /**
     * 查询客户数据库，并统计出数据写入统计表中
     * @param sqlDomain
     * @param startTime
     * @param endTime
     * @return
     */
    public ReturnValueDomain<Object> statisticsMethod(SqlDomain sqlDomain,String startTime,String endTime){
        ReturnValueDomain<Object> ret = new ReturnValueDomain<>();
        if(NonUtil.isNon(sqlDomain.getSql())){
            return ret.setSuccess("成功");
        }

        // 记录运行时间
        String statdate = DateUtil.getNowTime("yyyy-MM-dd");
        String runStartTime = DateUtil.getNowTime();
        String runEndTime = null;
        // 得到SQL对应字段的统计数目
        Double count = statisticsJsuset(sqlDomain,startTime,endTime,statdate,runStartTime);
        // 拼接UpdateSQL并写入对应的统计表中
        List<VariableDomain> baselist = sqlDomain.getBaselist();
        StringBuffer tableSql = new StringBuffer();
        StringBuffer updateColumnSql = new StringBuffer();
        StringBuffer whereSql = new StringBuffer();
        whereSql.append(" where 1 = 1 ");
        tableSql.append(" update ").append(sqlDomain.getTableName()).append(" set ").append(sqlDomain.getColumnname()).append(" = ").append(count);
        for(VariableDomain baseDomain :baselist){
            if("是".equals(baseDomain.getIsprkey())){
                if(baseDomain.getColumnname().equals("date_time")){
//                    whereSql.append(" and ").append(baseDomain.getColumnname()).append(" = ")
//                            .append("TO_DATE('").append(baseDomain.getValuescript()).append("','yyyy-mm-dd') ");
                    whereSql.append(" and ").append(baseDomain.getColumnname()).append(" = ").append(baseDomain.getValuescript());
                }else {
                    whereSql.append(" and ").append(baseDomain.getColumnname()).append(" = '").append(baseDomain.getValuescript()).append("' ");
                }
            }else{
                if(baseDomain.getColumnname().equals("createtime")|| baseDomain.getColumnname().equals("updatetime")){
//                    updateColumnSql.append(" , ").append(baseDomain.getColumnname()).append(" = ")
//                            .append("TO_DATE('").append(baseDomain.getValuescript()).append("','yyyy-mm-dd') ");
                    updateColumnSql.append(" , ").append(baseDomain.getColumnname()).append(" = ").append(baseDomain.getValuescript());
                }else{
                    updateColumnSql.append(" , ").append(baseDomain.getColumnname()).append(" = '").append(baseDomain.getValuescript()).append("' ");
                }
            }
        }
        tableSql.append(updateColumnSql).append(whereSql);
        System.out.println("=====================================");
        // 插入自己的数据库
        System.out.println(updateColumnSql);
        System.out.println(whereSql);
        System.out.println(tableSql);
        System.out.println("=====================================");
        try {
            jdbcTemplate.updateNohis(tableSql.toString());
        }catch (Exception e){
            logService.addDataStatLog(sqlDomain.getDsid(),sqlDomain.getTableName(),sqlDomain.getColumnname(),statdate,runStartTime,runEndTime,"失败",e.toString());
            return ret.setFail(sqlDomain.getTableName()+"字段:"+sqlDomain.getColumnname()+"修改数据失败");
        }
        runEndTime = DateUtil.getNowTime();
        logService.addDataStatLog(sqlDomain.getDsid(),sqlDomain.getTableName(),sqlDomain.getColumnname(),statdate,runStartTime,runEndTime,"成功","");
        return ret.setSuccess(statdate+sqlDomain.getTableName()+sqlDomain.getColumnname()+"修改数据成功");
    }

    /**
     * 得到SQL对应字段的统计数目
     * @param sqlDomain
     * @param startTime
     * @param endTime
     * @param statdate
     * @param runStartTime
     * @return
     */
    public Double statisticsJsuset(SqlDomain sqlDomain,String startTime,String endTime,String statdate,String runStartTime){
        String selectSql = sqlDomain.getSql();
        System.out.println("selectSql:"+selectSql);
        List<CommonDataDomain> appsyslist = null;
//        selectSql = "select count(1) from qt_dialogue_content";
        // 将查询时间传入SQL
        selectSql = replaceRule(selectSql,startTime,endTime);

        // 查询客户数据库
        try{
//            throw new NullPointerException();
            appsyslist = wayJdbcTemplate.query(selectSql);
        }catch(Exception e){
            String runEndTime = DateUtil.getNowTime();
            logService.addDataStatLog(sqlDomain.getDsid(),sqlDomain.getTableName(),sqlDomain.getColumnname(),statdate,runStartTime,runEndTime,"失败",e.toString());
        }
        // 将查出的所有数据相加，得到统计数目
        Double count = 0.0 ;
        // 循环取出每条数据
        for(CommonDataDomain dataDomain :appsyslist){
            Set<Map.Entry<String, Object>> entrySet = dataDomain.entrySet();
            // 累计该数据的每条字段对应的值
            for(Map.Entry<String, Object> countEntry :entrySet){
                String string = String.valueOf(countEntry.getValue());
                count = count + Double.valueOf(string);
            }
        }
        return count;
    }

    public String replaceRule(String selectSql,String startTime,String endTime){
        // 若是开始时间或结束时间为空，则默认查询前一天的数据
//        if( null == startTime ){
        SimpleDateFormat startSdf = new SimpleDateFormat("yyyy-MM-dd 00:00:00");
        SimpleDateFormat endSdf = new SimpleDateFormat("yyyy-MM-dd 23:59:59");
        Calendar calendar = Calendar.getInstance();
        calendar.add(Calendar.DATE, -1);
        startTime = startSdf.format(calendar.getTime());
        endTime = endSdf.format(calendar.getTime());
        String startTime1 = "TO_DATE(" + startTime + ",'yyyy-mm-dd')";
        String endTime1 = "TO_DATE(" + endTime + ",'yyyy-mm-dd')";
//        }
        // 正则表达式中匹配sql是否要替换日期startKey,endKey，若有则替换
        String replaceStr = selectSql.replace(Constant.startTimeKey1,startTime1).replace(Constant.endTimeKey1,endTime1)
                .replace(Constant.startTimeKey2,startTime).replace(Constant.endTimeKey2,endTime);
        return replaceStr;
    }

}
